﻿using System.Collections.Generic;
using Budgetsch.Web.EntityModel;
using System.IO;
using System.Drawing;
using System.Linq;
using OfficeOpenXml;
using OfficeOpenXml.Drawing;
using OfficeOpenXml.Drawing.Chart;
using OfficeOpenXml.Style;

namespace Budgetsch.Web.Services
{
    public class ExcelGenerator
    {
        //static void Main(string[] args)
        //{
        //    IList<Income> incomes = new List<Income>();
        //    for (int i = 0; i < 20; i++)
        //    {
        //        incomes.Add(
        //            new Income
        //                {
        //                    Description = "Description" + i,
        //                    Comment = "Comment" + i,
        //                    Amount = i * 1000,
        //                    IsRecurring = i % 3 == 0,
        //                    Delta = i % 3 == 0 ? i % 5 : 0,
        //                    IncomeCategory = new IncomeCategory { Name = "Category" + i % 4 },
        //                    StartDate = DateTime.Now.AddDays(i % 4),
        //                    EndDate = i % 3 == 0 ? DateTime.Now : (DateTime?)null
        //                });
        //    }
        //    //Dictionary<string, List<Income>> dt = (from inc in incomes
        //    //                                       group inc by inc.IncomeCategory.Name
        //    //                                           into g
        //    //                                           select new { Category = g.Key, Incomes = g.ToList() }).ToDictionary(g => g.Category, g => g.Incomes);
        //    //var d=RunSample(dt);

        //}


        public static byte[] GenerateExcel(IEnumerable<Income> incomeList)
        {
            //var newFile = new FileInfo(outputDir.FullName + @"\" + Guid.NewGuid() + ".xlsx");
            //if (newFile.Exists)
            //{
            //    newFile.Delete();  // ensures we create a new workbook
            //    newFile = new FileInfo(outputDir.FullName + @"\" + Guid.NewGuid() + ".xlsx");
            //}

            Dictionary<string, List<Income>> resultSet = (from inc in incomeList
                                                          group inc by inc.IncomeCategory.Name
                                                              into g
                                                              select new { Category = g.Key, Incomes = g.ToList() }).ToDictionary(g => g.Category, g => g.Incomes);

            var memoryStream = new MemoryStream();
            using (ExcelPackage package = new ExcelPackage(memoryStream))
            {
                // add a new worksheet to the empty workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Current month");
                //Add the headers
                worksheet.Cells[1, 1].Value = "Category";
                worksheet.Cells[1, 2].Value = "Description";
                worksheet.Cells[1, 3].Value = "IsRecurring";
                worksheet.Cells[1, 4].Value = "Occured(in weeks)";
                worksheet.Cells[1, 5].Value = "Start date";
                worksheet.Cells[1, 6].Value = "End date";
                worksheet.Cells[1, 7].Value = "Amount($)";

                worksheet.Row(1).Height = 25;
                var headerRow = worksheet.Cells[1, 1, 1, 7];
                headerRow.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                headerRow.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                var fill = headerRow.Style.Fill;
                fill.PatternType = ExcelFillStyle.Solid;

                var darkBackgroundColor = Color.FromArgb(118, 147, 60);
                fill.BackgroundColor.SetColor(darkBackgroundColor);
                headerRow.Style.Font.Color.SetColor(Color.White);

                int rowNum = 2;
                foreach (var category in resultSet.Keys)
                {
                    var incomes = resultSet[category];
                    worksheet.Cells[rowNum, 1].Value = category;
                    int from = rowNum;
                    int to = rowNum + incomes.Count - 1;
                    worksheet.Cells[rowNum, 1, to, 1].Merge = true;
                    worksheet.Cells[rowNum, 1, rowNum + incomes.Count, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    worksheet.Cells[rowNum, 1, rowNum + incomes.Count, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                    foreach (var income in incomes)
                    {
                        worksheet.Cells[rowNum, 2].Value = income.Description;

                        worksheet.Cells[rowNum, 3].Value = income.IsRecurring ? "yes" : "no";
                        worksheet.Cells[rowNum, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        worksheet.Cells[rowNum, 5].Value = income.StartDate;
                        if (income.IsRecurring)
                        {
                            worksheet.Cells[rowNum, 4].Value = income.Delta;
                            worksheet.Cells[rowNum, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                            worksheet.Cells[rowNum, 6].Value = income.EndDate;
                        }
                        worksheet.Cells[rowNum, 7].Value = income.Amount;
                        rowNum++;
                    }
                    var border = worksheet.Cells[from, 1, to, 7].Style.Border;
                    border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
                    border.Left.Color.SetColor(darkBackgroundColor);
                    border.Right.Color.SetColor(darkBackgroundColor);
                    border.Top.Color.SetColor(darkBackgroundColor);
                    border.Bottom.Color.SetColor(darkBackgroundColor);
                }

                var totalCell = worksheet.Cells[rowNum, 6, rowNum, 7];
                worksheet.Cells[rowNum, 6].Value = "Total:";
                worksheet.Cells[rowNum, 6].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                worksheet.Cells[rowNum, 7].Formula = "SUM(" + worksheet.Cells[2, 7].Address + ":" + worksheet.Cells[rowNum - 1, 7].Address + ")";

                totalCell.Style.Font.Bold = true;
                var totalBorder = totalCell.Style.Border;
                totalBorder.Bottom.Style = totalBorder.Top.Style = totalBorder.Left.Style = totalBorder.Right.Style = ExcelBorderStyle.Thin;
                totalBorder.Left.Color.SetColor(darkBackgroundColor);
                totalBorder.Right.Color.SetColor(darkBackgroundColor);
                totalBorder.Top.Color.SetColor(darkBackgroundColor);
                totalBorder.Bottom.Color.SetColor(darkBackgroundColor);


                //Ok now format the values;
                using (var range = worksheet.Cells[2, 5, rowNum - 1, 6])
                {
                    range.Style.Numberformat.Format = @"mm/dd/yyyy";
                }

                for (int i = 1; i < 8; i++)
                {
                    worksheet.Column(i).AutoFit();
                }

                var detailsSectionFill = worksheet.Cells[2, 2, rowNum - 1, 7].Style.Fill;
                detailsSectionFill.PatternType = ExcelFillStyle.Solid;
                detailsSectionFill.BackgroundColor.SetColor(Color.FromArgb(216, 228, 188));

                var categorySectionFill = worksheet.Cells[2, 1, rowNum - 1, 1].Style.Fill;
                categorySectionFill.PatternType = ExcelFillStyle.Solid;
                categorySectionFill.BackgroundColor.SetColor(Color.FromArgb(196, 215, 155));

                var chart = worksheet.Drawings.AddChart("ColumnChart", eChartType.ColumnClustered3D);

                //chart.Title.Text = "Incomes int the current month";
                //From row 1 colum 5 with five pixels offset
                chart.SetPosition(0, 5, 7, 5);
                chart.SetSize(700, 460);

                ExcelAddress valueAddress = new ExcelAddress(2, 7, rowNum - 1, 7);
                ExcelAddress textAddress = new ExcelAddress(2, 5, rowNum - 1, 5);
                var serie = chart.Series.Add(valueAddress.Address, textAddress.Address);
                serie.Header = "Sum incomes";


                chart.Legend.Border.LineStyle = eLineStyle.Solid;
                chart.Legend.Border.Fill.Style = eFillStyle.SolidFill;
                chart.Legend.Border.Fill.Color = Color.DodgerBlue;
                chart.Style = eChartStyle.Style5;

                //// lets set the header text 
                //worksheet.HeaderFooter.oddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Monthly report from incomes";

                // Change the sheet view to show it in page layout mode
                //worksheet.View.PageLayoutView = true;
                worksheet.View.ShowGridLines = false;

                // set some document properties
                package.Workbook.Properties.Title = "Income report";
                package.Workbook.Properties.Author = "BudgetSCH";
                package.Workbook.Properties.Comments = "This report conains incomes details of the current month";

                // set some extended property values
                package.Workbook.Properties.Company = "BudgetSCH";

                // set some custom property values
                package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Mikes Péter, Déri András");
                package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");

                // save our new workbook and we are done!
                package.Save();
            }
            return memoryStream.ToArray();
        }

        public static byte[] GenerateExcel(IEnumerable<Cost> costList)
        {
            //var newFile = new FileInfo(outputDir.FullName + @"\" + Guid.NewGuid() + ".xlsx");
            //if (newFile.Exists)
            //{
            //    newFile.Delete();  // ensures we create a new workbook
            //    newFile = new FileInfo(outputDir.FullName + @"\" + Guid.NewGuid() + ".xlsx");
            //}

            Dictionary<string, List<Cost>> resultSet = (from cost in costList
                                                        group cost by cost.CostCategory.Name
                                                            into g
                                                            select new { Category = g.Key, Costs = g.ToList() }).ToDictionary(g => g.Category, g => g.Costs);

            var memoryStream = new MemoryStream();
            using (ExcelPackage package = new ExcelPackage(memoryStream))
            {
                // add a new worksheet to the empty workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Current month");
                //Add the headers
                worksheet.Cells[1, 1].Value = "Category";
                worksheet.Cells[1, 2].Value = "Description";
                worksheet.Cells[1, 3].Value = "IsRecurring";
                worksheet.Cells[1, 4].Value = "Occured(in weeks)";
                worksheet.Cells[1, 5].Value = "Start date";
                worksheet.Cells[1, 6].Value = "End date";
                worksheet.Cells[1, 7].Value = "Amount($)";

                worksheet.Row(1).Height = 25;
                var headerRow = worksheet.Cells[1, 1, 1, 7];
                headerRow.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                headerRow.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                var fill = headerRow.Style.Fill;
                fill.PatternType = ExcelFillStyle.Solid;

                var darkBackgroundColor = Color.FromArgb(150, 54, 52);
                fill.BackgroundColor.SetColor(darkBackgroundColor);
                headerRow.Style.Font.Color.SetColor(Color.White);

                int rowNum = 2;
                foreach (var category in resultSet.Keys)
                {
                    var costs = resultSet[category];
                    worksheet.Cells[rowNum, 1].Value = category;
                    int from = rowNum;
                    int to = rowNum + costs.Count - 1;
                    worksheet.Cells[rowNum, 1, to, 1].Merge = true;
                    worksheet.Cells[rowNum, 1, rowNum + costs.Count, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    worksheet.Cells[rowNum, 1, rowNum + costs.Count, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                    foreach (var cost in costs)
                    {
                        worksheet.Cells[rowNum, 2].Value = cost.Description;

                        worksheet.Cells[rowNum, 3].Value = cost.IsRecurring ? "yes" : "no";
                        worksheet.Cells[rowNum, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                        worksheet.Cells[rowNum, 5].Value = cost.StartDate;
                        if (cost.IsRecurring)
                        {
                            worksheet.Cells[rowNum, 4].Value = cost.Delta;
                            worksheet.Cells[rowNum, 4].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                            worksheet.Cells[rowNum, 6].Value = cost.EndDate;
                        }
                        worksheet.Cells[rowNum, 7].Value = cost.Amount;
                        rowNum++;
                    }
                    var border = worksheet.Cells[from, 1, to, 7].Style.Border;
                    border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
                    border.Left.Color.SetColor(darkBackgroundColor);
                    border.Right.Color.SetColor(darkBackgroundColor);
                    border.Top.Color.SetColor(darkBackgroundColor);
                    border.Bottom.Color.SetColor(darkBackgroundColor);
                }

                var totalCell = worksheet.Cells[rowNum, 6, rowNum, 7];
                worksheet.Cells[rowNum, 6].Value = "Total:";
                worksheet.Cells[rowNum, 6].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                worksheet.Cells[rowNum, 7].Formula = "SUM(" + worksheet.Cells[2, 7].Address + ":" + worksheet.Cells[rowNum - 1, 7].Address + ")";

                totalCell.Style.Font.Bold = true;
                var totalBorder = totalCell.Style.Border;
                totalBorder.Bottom.Style = totalBorder.Top.Style = totalBorder.Left.Style = totalBorder.Right.Style = ExcelBorderStyle.Thin;
                totalBorder.Left.Color.SetColor(darkBackgroundColor);
                totalBorder.Right.Color.SetColor(darkBackgroundColor);
                totalBorder.Top.Color.SetColor(darkBackgroundColor);
                totalBorder.Bottom.Color.SetColor(darkBackgroundColor);


                //Ok now format the values;
                using (var range = worksheet.Cells[2, 5, rowNum - 1, 6])
                {
                    range.Style.Numberformat.Format = @"mm/dd/yyyy";
                }

                for (int i = 1; i < 8; i++)
                {
                    worksheet.Column(i).AutoFit();
                }

                var detailsSectionFill = worksheet.Cells[2, 2, rowNum - 1, 7].Style.Fill;
                detailsSectionFill.PatternType = ExcelFillStyle.Solid;
                var detailsColor = Color.FromArgb(230, 184, 183);
                detailsSectionFill.BackgroundColor.SetColor(detailsColor);

                var categorySectionFill = worksheet.Cells[2, 1, rowNum - 1, 1].Style.Fill;
                categorySectionFill.PatternType = ExcelFillStyle.Solid;
                var categoryColor = Color.FromArgb(218, 150, 148);
                categorySectionFill.BackgroundColor.SetColor(categoryColor);

                var chart = worksheet.Drawings.AddChart("ColumnChart", eChartType.ColumnClustered3D);

                //chart.Title.Text = "Incomes int the current month";
                //From row 1 colum 5 with five pixels offset
                chart.SetPosition(0, 5, 7, 5);
                chart.SetSize(700, 460);

                ExcelAddress valueAddress = new ExcelAddress(2, 7, rowNum - 1, 7);
                ExcelAddress textAddress = new ExcelAddress(2, 5, rowNum - 1, 5);
                var serie = chart.Series.Add(valueAddress.Address, textAddress.Address);
                serie.Header = "Sum costs";


                chart.Legend.Border.LineStyle = eLineStyle.Solid;
                chart.Legend.Border.Fill.Style = eFillStyle.SolidFill;
                chart.Legend.Border.Fill.Color = Color.IndianRed;
                chart.Style = eChartStyle.Style4;

                //// lets set the header text 
                //worksheet.HeaderFooter.oddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Monthly report from incomes";

                // Change the sheet view to show it in page layout mode
                //worksheet.View.PageLayoutView = true;
                worksheet.View.ShowGridLines = false;

                // set some document properties
                package.Workbook.Properties.Title = "Income report";
                package.Workbook.Properties.Author = "BudgetSCH";
                package.Workbook.Properties.Comments = "This report conains costs details of the current month";

                // set some extended property values
                package.Workbook.Properties.Company = "BudgetSCH";

                // set some custom property values
                package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Mikes Péter, Déri András");
                package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");

                // save our new workbook and we are done!
                package.Save();
            }
            return memoryStream.ToArray();
        }

        public static byte[] GenerateExcel(IEnumerable<Saving> savingList)
        {
            //var newFile = new FileInfo(outputDir.FullName + @"\" + Guid.NewGuid() + ".xlsx");
            //if (newFile.Exists)
            //{
            //    newFile.Delete();  // ensures we create a new workbook
            //    newFile = new FileInfo(outputDir.FullName + @"\" + Guid.NewGuid() + ".xlsx");
            //}

            Dictionary<string, List<Saving>> resultSet = (from saving in savingList
                                                          group saving by saving.SavingCategory.Name
                                                              into g
                                                              select new { Category = g.Key, Savings = g.ToList() }).ToDictionary(g => g.Category, g => g.Savings);

            var memoryStream = new MemoryStream();
            using (ExcelPackage package = new ExcelPackage(memoryStream))
            {
                // add a new worksheet to the empty workbook
                ExcelWorksheet worksheet = package.Workbook.Worksheets.Add("Current month");
                //Add the headers
                worksheet.Cells[1, 1].Value = "Category";
                worksheet.Cells[1, 2].Value = "Comment";
                worksheet.Cells[1, 3].Value = "Date";
                worksheet.Cells[1, 4].Value = "Amount($)";

                worksheet.Row(1).Height = 25;
                var headerRow = worksheet.Cells[1, 1, 1, 4];
                headerRow.Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;
                headerRow.Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                var fill = headerRow.Style.Fill;
                fill.PatternType = ExcelFillStyle.Solid;

                var darkBackgroundColor = Color.FromArgb(226, 107, 10);
                fill.BackgroundColor.SetColor(darkBackgroundColor);
                headerRow.Style.Font.Color.SetColor(Color.White);

                int rowNum = 2;
                foreach (var category in resultSet.Keys)
                {
                    var savings = resultSet[category];
                    worksheet.Cells[rowNum, 1].Value = category;
                    int from = rowNum;
                    int to = rowNum + savings.Count - 1;
                    worksheet.Cells[rowNum, 1, to, 1].Merge = true;
                    worksheet.Cells[rowNum, 1, rowNum + savings.Count, 1].Style.VerticalAlignment = ExcelVerticalAlignment.Center;
                    worksheet.Cells[rowNum, 1, rowNum + savings.Count, 1].Style.HorizontalAlignment = ExcelHorizontalAlignment.Center;

                    foreach (var saving in savings)
                    {
                        worksheet.Cells[rowNum, 2].Value = saving.Comment;
                        worksheet.Cells[rowNum, 3].Value = saving.Date;
                        worksheet.Cells[rowNum, 4].Value = saving.Amount;
                        rowNum++;
                    }
                    var border = worksheet.Cells[from, 1, to, 4].Style.Border;
                    border.Bottom.Style = border.Top.Style = border.Left.Style = border.Right.Style = ExcelBorderStyle.Thin;
                    border.Left.Color.SetColor(darkBackgroundColor);
                    border.Right.Color.SetColor(darkBackgroundColor);
                    border.Top.Color.SetColor(darkBackgroundColor);
                    border.Bottom.Color.SetColor(darkBackgroundColor);
                }

                var totalCell = worksheet.Cells[rowNum, 3, rowNum, 4];
                worksheet.Cells[rowNum, 3].Value = "Total:";
                worksheet.Cells[rowNum, 3].Style.HorizontalAlignment = ExcelHorizontalAlignment.Right;
                worksheet.Cells[rowNum, 4].Formula = "SUM(" + worksheet.Cells[2, 4].Address + ":" + worksheet.Cells[rowNum - 1, 4].Address + ")";

                totalCell.Style.Font.Bold = true;
                var totalBorder = totalCell.Style.Border;
                totalBorder.Bottom.Style = totalBorder.Top.Style = totalBorder.Left.Style = totalBorder.Right.Style = ExcelBorderStyle.Thin;
                totalBorder.Left.Color.SetColor(darkBackgroundColor);
                totalBorder.Right.Color.SetColor(darkBackgroundColor);
                totalBorder.Top.Color.SetColor(darkBackgroundColor);
                totalBorder.Bottom.Color.SetColor(darkBackgroundColor);


                //Ok now format the values;
                using (var range = worksheet.Cells[2, 3, rowNum - 1, 3])
                {
                    range.Style.Numberformat.Format = @"mm/dd/yyyy";
                }

                for (int i = 1; i < 4; i++)
                {
                    worksheet.Column(i).AutoFit();
                }

                var detailsSectionFill = worksheet.Cells[2, 2, rowNum - 1, 4].Style.Fill;
                detailsSectionFill.PatternType = ExcelFillStyle.Solid;
                var detailsColor = Color.FromArgb(252, 213, 180);
                detailsSectionFill.BackgroundColor.SetColor(detailsColor);

                var categorySectionFill = worksheet.Cells[2, 1, rowNum - 1, 1].Style.Fill;
                categorySectionFill.PatternType = ExcelFillStyle.Solid;
                var categoryColor = Color.FromArgb(218, 150, 148);
                categorySectionFill.BackgroundColor.SetColor(categoryColor);

                var chart = worksheet.Drawings.AddChart("ColumnChart", eChartType.ColumnClustered3D);

                //chart.Title.Text = "Incomes int the current month";
                //From row 1 colum 5 with five pixels offset
                chart.SetPosition(0, 5, 4, 5);
                chart.SetSize(700, 460);

                ExcelAddress valueAddress = new ExcelAddress(2, 4, rowNum - 1, 4);
                ExcelAddress textAddress = new ExcelAddress(2, 3, rowNum - 1, 3);
                var serie = chart.Series.Add(valueAddress.Address, textAddress.Address);
                serie.Header = "Sum savings";


                chart.Legend.Border.LineStyle = eLineStyle.Solid;
                chart.Legend.Border.Fill.Style = eFillStyle.SolidFill;
                chart.Legend.Border.Fill.Color = Color.DarkOrange;
                chart.Style = eChartStyle.Style8;

                //// lets set the header text 
                //worksheet.HeaderFooter.oddHeader.CenteredText = "&24&U&\"Arial,Regular Bold\" Monthly report from incomes";

                // Change the sheet view to show it in page layout mode
                //worksheet.View.PageLayoutView = true;
                worksheet.View.ShowGridLines = false;

                // set some document properties
                package.Workbook.Properties.Title = "Income report";
                package.Workbook.Properties.Author = "BudgetSCH";
                package.Workbook.Properties.Comments = "This report conains costs details of the current month";

                // set some extended property values
                package.Workbook.Properties.Company = "BudgetSCH";

                // set some custom property values
                package.Workbook.Properties.SetCustomPropertyValue("Checked by", "Mikes Péter, Déri András");
                package.Workbook.Properties.SetCustomPropertyValue("AssemblyName", "EPPlus");

                // save our new workbook and we are done!
                package.Save();
            }
            return memoryStream.ToArray();
        }
    }
}
